"""
从SQLITE中读取配置,保存配置
"""

from DBUtils.PooledDB import PooledDB
import sqlite3

from top.wardyan.core.vo.vos import *

# 定义数据库文件路径
db_file = 'config\\config.db'


# 创建SQLite连接池
SQLITEPOOL = PooledDB(
    creator=sqlite3,  # 使用sqlite3模块
    maxconnections=5,  # 连接池允许的最大连接数
    mincached=1,  # 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached=5,  # 链接池中最多闲置的链接，0和None不限制
    maxshared=3,  # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为sqlite3连接不能共享
    blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    database=db_file  # 数据库文件名
    , check_same_thread = False
)


SQL_FINDWINDOW = "SELECT id,name,title,isFullScreen,width,height,x,y,isMoveable,position,icon FROM yt_window where name = ?"
SQL_LISTTABLES = "SELECT id,name,winID,cols,rowHeight,colMoveable FROM yt_table where winID = ?"
SQL_LISTCOLS = "SELECT tableID,id,idx,field,title,align,width,show,replace,datatype,format,sql,dicID FROM yt_table_col where tableID = ?"

def readConfig(windowname : str) -> WinCfg :
    """
    2024-06-16 F 从SQLITE中读取某个窗口的所有配置
    要不要搞缓存?
    """
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    cursor.execute(SQL_FINDWINDOW, (windowname,))
    wc = cursor.fetchone()

    wincfg : WinCfg = WinCfg()
    
    if wc is not None :
        wincfg.id = wc[0]
        wincfg.name = wc[1]
        wincfg.title = wc[2]
        wincfg.isFullScreen = wc[3]
        wincfg.width = wc[4]
        wincfg.height = wc[5]
        wincfg.x = wc[6]
        wincfg.y = wc[7]
        wincfg.isMoveable = wc[8]
        wincfg.position = wc[9]
        wincfg.icon = wc[10]

        wincfg.tables = []
        ## tables
        cursor.execute(SQL_LISTTABLES, (wincfg.id,))
        ts = cursor.fetchall()
        if ts is not None and len(ts) > 0 :
            for t in ts :
                tc : TableCfg = TableCfg()
                tc.id = t[0]
                tc.name = t[1]
                tc.winID = t[2]
                tc.fields = t[3]
                tc.height = t[4]
                tc.colmoveable = t[5]
                tc.cols = []
                
                cursor.execute(SQL_LISTCOLS, (tc.id,))
                cs = cursor.fetchall()
                if cs is not None and len(cs) > 0 :
                    for c in cs :
                        cc : TableColCfg = TableColCfg()
                        cc.tableID = c[0]
                        cc.id = c[1]
                        cc.idx = c[2]
                        cc.field = c[3]
                        cc.title = c[4]
                        cc.align = c[5]
                        cc.width = c[6]
                        cc.show = c[7]
                        cc.replace = c[8]
                        cc.datatype = c[9]
                        cc.format = c[10]
                        cc.sql = c[11]
                        cc.dicID = c[12]
                        tc.cols.append(cc)
                wincfg.tables.append(tc)
    else :
        wincfg.name = windowname
        ## saveWindowCfg(wincfg)
        ## saveConfigs(wincfg)
    
    cursor.close()
    db.close()
    return wincfg


def _validateConfigsWin(wincfg : WinCfg , cfgs : WinCfg)  -> WinCfg :
    """
    2024-06-18 F 保存窗口配置:窗口
    wincfg : 待保存的
    cfgs : 读取的
    """
    # if cfgs is None :
    #     cfgs = wincfg
    # if wincfg.id is not None and wincfg.id > 0 :
    #     cfgs.id = wincfg.id
    # if wincfg.title is not None  and wincfg.title != "" :
    #     cfgs.title = wincfg.title
    # if wincfg.name is not None   and wincfg.name != "" :
    #     cfgs.name = wincfg.name
    # if wincfg.isFullScreen is not None :
    #     cfgs.isFullScreen = wincfg.isFullScreen
    # if wincfg.width is not None and wincfg.width > 0 :
    #     cfgs.width = wincfg.width
    # if wincfg.height is not None and wincfg.height > 0 :
    #     cfgs.height = wincfg.height
    # if wincfg.x is not None and wincfg.x > 0 :
    #     cfgs.x = wincfg.x
    # if wincfg.y is not None and wincfg.y > 0 :
    #     cfgs.y = wincfg.y
    # if wincfg.isMoveable is not None :
    #     cfgs.isMoveable = wincfg.isMoveable
    # if wincfg.position is not None and wincfg.position > 0 :
    #     cfgs.position = wincfg.position
    if cfgs is not None and cfgs.id > 0 :
        ## update
        sql = "update yt_window set title = ?,isFullScreen = ?,width = ?,height = ?,x = ?,y = ?,isMoveable = ?,position = ?,icon = ? where id = ?"
        db = SQLITEPOOL.connection()
        cursor = db.cursor()
        cursor.execute(sql, (wincfg.title,wincfg.isFullScreen,wincfg.width,wincfg.height,wincfg.x,wincfg.y,wincfg.isMoveable,wincfg.position,wincfg.icon,wincfg.id))
        db.commit()
        cursor.close()
        db.close()
        cfgs = readConfig(wincfg.name)
    else :
        ## 插入
        ## 先生成一个ID
        sql = "SELECT max(id) as id from yt_window"
        db = SQLITEPOOL.connection()
        cursor = db.cursor()
        cursor.execute(sql)
        id = cursor.fetchone()
        if id is not None and id[0] is not None :
            wincfg.id = id[0] + 1
        else :
            wincfg.id = 1
        cfgs.id = wincfg.id
        sql = "INSERT INTO yt_window(id,name,title,isFullScreen,width,height,x,y,isMoveable,position,icon) values(?,?,?,?,?,?,?,?,?,?,?)"
        cursor.execute(sql, (wincfg.id,wincfg.name,wincfg.title,wincfg.isFullScreen,wincfg.width,wincfg.height,wincfg.x,wincfg.y,wincfg.isMoveable,wincfg.position,wincfg.icon))
        db.commit()
        cursor.close()
        db.close()
        cfgs = readConfig(wincfg.name)
    return cfgs


def _get_table(cfgs : WinCfg, name : str) -> TableCfg : 
    for table in cfgs.tables :
        if table.name == name :
            return table
    return None

def _validateConfigsTables(wincfg : WinCfg , cfgs : WinCfg)  -> WinCfg :
    """
    2024-06-18 F 保存窗口配置:窗口
    wincfg : 待保存的
    cfgs : 读取的
    """
    if cfgs is None :
        cfgs = wincfg
    tables = wincfg.tables
    ntables = []
    ##sql = "DELETE FROM yt_table where winID = ?"
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    ##cursor.execute(sql, (wincfg.id,))
    ##db.commit()
    ## cfgs = _validateConfigsWin(wincfg, cfgs)
    
    for table in  tables:
        name = table.name
        sql = "SELECT id FROM yt_table where winID = ? and name = ?"
        cursor.execute(sql, (cfgs.id,name))
        t = cursor.fetchone()
        if t is not None :
            table.id = t[0]
            sql = "UPDATE yt_table set cols = ?,rowHeight = ?,colMoveable = ? where winID = ? and name = ?"
            cursor.execute(sql, (table.fields,table.height,table.colmoveable,table.id,name))
            db.commit()
        else :
            ## 先生成ID
            sql = "SELECT max(id) as id from yt_table"
            cursor.execute(sql)
            id = cursor.fetchone()
            if id is not None and id[0] is not None :
                table.id = id[0] + 1
            else :
                table.id = 1
            sql = "INSERT INTO yt_table(id,winID,name,cols,rowHeight,colMoveable) values(?,?,?,?,?,?)"
            cursor.execute(sql, (table.id,cfgs.id,name,table.fields,table.height,table.colmoveable))
            db.commit()

        ## 处理明细表 表格列列
        sql = "DELETE FROM yt_table_col where tableID = ?"
        cursor.execute(sql, (table.id,))
        db.commit()

        
        cols = table.cols
        for col in cols :
            sql = "INSERT INTO yt_table_col(id,tableID,id,idx,field,title,align,width,show,replace,datatype,format,sql) values(?,?,?,?,?,?,?,?,?,?,?,?,?)"
            cursor.execute(sql, (col.idx,table.id,col.idx,col.idx,col.field,col.title,col.align,col.width,col.show,col.replace,col.datatype,col.format,col.sql))
            db.commit()


    cursor.close()
    db.close()
    
    cfgs = readConfig(cfgs.name)
    return cfgs





# def _clearConfigsTables(wincfg : WinCfg , cfgs : WinCfg, tablename : str)  -> WinCfg :
#     """
#     2024-06-18 F 保存窗口配置:窗口
#     wincfg : 待保存的
#     cfgs : 读取的
#     """
#     if cfgs is None :
#         cfgs = wincfg
#     tables = wincfg.tables
#     ntables = []
#     ##sql = "DELETE FROM yt_table where winID = ?"
#     db = SQLITEPOOL.connection()
#     cursor = db.cursor()
#     ##cursor.execute(sql, (wincfg.id,))
#     ##db.commit()
#     ## cfgs = _validateConfigsWin(wincfg, cfgs)
    
#     for table in  tables:
#         name = table.name
#         sql = "SELECT id FROM yt_table where winID = ? and name = ?"
#         cursor.execute(sql, (cfgs.id,name))
#         t = cursor.fetchone()
#         if t is not None :
#             table.id = t[0]
#             sql = "UPDATE yt_table set cols = ?,rowHeight = ?,colMoveable = ? where winID = ? and name = ?"
#             cursor.execute(sql, (table.fields,table.height,table.colmoveable,table.id,name))
#             db.commit()
#         else :
#             ## 先生成ID
#             sql = "SELECT max(id) as id from yt_table"
#             cursor.execute(sql)
#             id = cursor.fetchone()
#             if id is not None and id[0] is not None :
#                 table.id = id[0] + 1
#             else :
#                 table.id = 1
#             sql = "INSERT INTO yt_table(id,winID,name,cols,rowHeight,colMoveable) values(?,?,?,?,?,?)"
#             cursor.execute(sql, (table.id,cfgs.id,name,table.fields,table.height,table.colmoveable))
#             db.commit()

#         ## 处理明细表 表格列列
#         sql = "DELETE FROM yt_table_col where tableID = ?"
#         cursor.execute(sql, (table.id,))
#         db.commit()

        
#         cols = table.cols
#         for col in cols :
#             sql = "INSERT INTO yt_table_col(id,tableID,id,idx,field,title,align,width,show,replace,datatype,format,sql) values(?,?,?,?,?,?,?,?,?,?,?,?,?)"
#             cursor.execute(sql, (col.idx,table.id,col.idx,col.idx,col.field,col.title,col.align,col.width,col.show,col.replace,col.datatype,col.format,col.sql))
#             db.commit()


#     cursor.close()
#     db.close()
    
#     cfgs = readConfig(cfgs.name)
#     return cfgs


    
def saveConfigs(wincfg : WinCfg) :
    """
    保存配置
    """
    name = wincfg.name

    cfgs = readConfig(name)
    ## 窗口的
    cfgs = _validateConfigsWin(wincfg, cfgs)

    ## 表格的
    cfgs = _validateConfigsTables(wincfg, cfgs)

    ## 表格列的
    ## cfgs = _validateConfigsTablesCols(wincfg, cfgs)

    ## 保存



def clearTableCfg(widgetname : str, tablename : str) :
    # cfgs = readConfig(widgetname)
    # ## table = _get_table(cfgs, tbalename)
    # ## 暂时只支持窗口内只有一个表格的
    # cfgs.tables = []
    # saveConfigs(cfgs)
    """
    2024-06-18 F 保存窗口配置:窗口
    wincfg : 待保存的
    cfgs : 读取的
    """
    cfgs = readConfig(widgetname)
    table = _get_table(cfgs, tablename)

    ntables = []
    ##sql = "DELETE FROM yt_table where winID = ?"
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    
    # sql = "SELECT id FROM yt_table where winID = ? and name = ?"
    # cursor.execute(sql, (cfgs.id,tablename))
    # t = cursor.fetchone()
    # if t is not None :
    #     table.id = t[0]
    #     sql = "UPDATE yt_table set cols = ?,rowHeight = ?,colMoveable = ? where winID = ? and name = ?"
    #     cursor.execute(sql, ("",table.height,table.colmoveable,table.id,tablename))
    #     db.commit()
    # else :
    #     ## 先生成ID
    #     sql = "SELECT max(id) as id from yt_table"
    #     cursor.execute(sql)
    #     id = cursor.fetchone()
    #     if id is not None and id[0] is not None :
    #         table.id = id[0] + 1
    #     else :
    #         table.id = 1
    #     sql = "INSERT INTO yt_table(id,winID,name,cols,rowHeight,colMoveable) values(?,?,?,?,?,?)"
    #     cursor.execute(sql, (table.id,cfgs.id,name,table.fields,table.height,table.colmoveable))
    #     db.commit()

    ## 处理明细表 表格列列
    sql = "DELETE FROM yt_table_col where tableID = ?"
    cursor.execute(sql, (table.id,))

    sql = "UPDATE yt_table SET cols = '' WHERE id = ?"
    cursor.execute(sql, (table.id,))
    db.commit()

    
    # cols = table.cols
    # for col in cols :
    #     sql = "INSERT INTO yt_table_col(id,tableID,id,idx,field,title,align,width,show,replace,datatype,format,sql) values(?,?,?,?,?,?,?,?,?,?,?,?,?)"
    #     cursor.execute(sql, (col.idx,table.id,col.idx,col.idx,col.field,col.title,col.align,col.width,col.show,col.replace,col.datatype,col.format,col.sql))
    #     db.commit()


    cursor.close()
    db.close()
    
    cfgs = readConfig(cfgs.name)
    return cfgs



def getTableCfg(widgetname : str, tablename : str) -> TableCfg :
    """
    读取配置
    """
    cfgs = readConfig(widgetname)
    table = _get_table(cfgs, tablename)
    if table is None :
        table = TableCfg()
        table.name = tablename
    return table

def saveTableCfg(widgetname : str, tbalename : str, tablecfg : TableCfg) :
    cfgs = readConfig(widgetname)
    ## table = _get_table(cfgs, tbalename)
    ## 暂时只支持窗口内只有一个表格的
    cfgs.tables = []
    cfgs.tables.append(tablecfg)
    saveConfigs(cfgs)


def saveTableColCfg(colcfg :TableColCfg) :
    sql = "UPDATE yt_table_col set title = ?,align = ?,width = ?,show = ?,replace = ?,datatype = ?,format = ?,sql = ?, idx = ?, dicID = ? where tableID = ? AND id = ?"
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    
    cursor.execute(sql, (colcfg.title,colcfg.align,colcfg.width,colcfg.show,colcfg.replace,colcfg.datatype,colcfg.format,colcfg.sql, colcfg.idx, colcfg.dicID, colcfg.tableID, colcfg.id))
    db.commit()
    cursor.close()
    db.close()



def readDicts(id : int) -> dict :
    """
    值 : 显示内容 
    """
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    rs : dict = {}
    cursor.execute("select dicID, key, value, note from yt_dict_item where dicID = ? ORDER BY idx", (id,))
    data = cursor.fetchall()
    ## 
    if data is not None :
        for row in data :
            rs[row[2]] = row[1]
    cursor.close()
    db.close()
    return rs

def readDicts_reverse(id : int) -> dict :
    """
    显示内容 : 值
    """
    rs = readDicts(id)
    rs = reverse_dict(rs)
    return rs


def reverse_dict(d : dict) -> dict :
    return {v: k for k, v in d.items()}




def exec(sql : str, params : list = []) :
    """
    执行SQL,没有结果返回的那种
    """
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    cursor.execute(sql, params)
    db.commit()
    cursor.close()
    db.close()

def query(sql : str, params : list = []):
    """
    执行查询
    """
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    cursor.execute(sql, params)
    rs = cursor.fetchall()
    cursor.close()
    db.close()
    return rs

def list_objectlist() :
    rs = query("select * from yt_objectlist")
    return rs


"""
写入: 
CREATE TABLE yt_databasewatch (
	sID INTEGER auto_increment  PRIMARY KEY,
    xCost INTEGER,
    xThreadCnt INTEGER,
    xCompleteRate REAL,
    xSessionID INTEGER,
    xStatus TEXT,
    xWaitingType TEXT,
    xName TEXT,
    xSQL TEXT,
    xSource TEXT,
    xStartTime TEXT,
    xBlockingBy INTEGER,
    xWaitingTime INTEGER,
    xPhysicalRead INTEGER,
    xWriteCnt INTEGER,
    xLogicalRead INTEGER,
    xRowCnt INTEGER,
    xCPUCost INTEGER,
    xDatabase TEXT,
    xUser TEXT,
    xCmd TEXT,
    xProgram TEXT,
    xHost TEXT
);
"""

_sql_insert_databasewatch_="""
INSERT INTO yt_databasewatch(xCost,xThreadCnt,xCompleteRate,xSessionID,xStatus,xWaitingType,xName,xSQL,xSource,xStartTime,xBlockingBy,xWaitingTime,xPhysicalRead,xWriteCnt,xLogicalRead,xRowCnt,xCPUCost,xDatabase,xUser,xCmd,xProgram,xHost) 
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
_sql_insert_killlog_="""
INSERT INTO yt_killlog(xDateTime,xCost,xThreadCnt,xCompleteRate,xSessionID,xStatus,xWaitingType,xName,xSQL,xSource,xStartTime,xBlockingBy,xWaitingTime,xPhysicalRead,xWriteCnt,xLogicalRead,xRowCnt,xCPUCost,xDatabase,xUser,xCmd,xProgram,xHost) 
values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
"""
def save_databasewatch(data : dict) :
    
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    cursor.execute(_sql_insert_databasewatch_, (data["xCost"],data["xThreadCnt"],data["xCompleteRate"],data["xSessionID"],data["xStatus"],data["xWaitingType"],data["xName"],data["xSQL"],data["xSource"],data["xStartTime"],data["xBlockingBy"],data["xWaitingTime"],data["xPhysicalRead"],data["xWriteCnt"],data["xLogicalRead"],data["xRowCnt"],data["xCPUCost"],data["xDatabase"],data["xUser"],data["xCmd"],data["xProgram"],data["xHost"]))
    db.commit()
    cursor.close()
    db.close()

def save_killlog(data : dict) :
    
    db = SQLITEPOOL.connection()
    cursor = db.cursor()
    cursor.execute(_sql_insert_killlog_, (data["xDateTime"],data["xCost"],data["xThreadCnt"],data["xCompleteRate"],data["xSessionID"],data["xStatus"],data["xWaitingType"],data["xName"],data["xSQL"],data["xSource"],data["xStartTime"],data["xBlockingBy"],data["xWaitingTime"],data["xPhysicalRead"],data["xWriteCnt"],data["xLogicalRead"],data["xRowCnt"],data["xCPUCost"],data["xDatabase"],data["xUser"],data["xCmd"],data["xProgram"],data["xHost"]))
    db.commit()
    cursor.close()
    db.close()


if __name__ == "__main__" :
    print(list_objectlist())
